package com.price.compare.common;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelImportUtil {


    public static final String xlsx = ".xlsx";
    public static final String XLSX = ".XLSX";
    public static final String xls = ".xls";
    public static final String XLS = ".XLS";

    public static final String xlsm = ".xlsm";
    public static final String XLSM = ".XLSM";

    public static final String csv = ".csv";
    public static final String CSV = ".CSV";


    /**
     * 判断在当前sheet中是否存在某个列名
     *
     * @param file      excel文件  如果为空 返回 false
     * @param sheetName sheet名字 默认第一个sheet
     * @param rowIndex  列名所在列  默认0
     * @param value     值  如果为空，返回true
     * @return
     */
    public static boolean hasValueInRowOfSheet(MultipartFile file, String sheetName, Integer rowIndex, String value) throws IOException {
        if (null == file) {
            return false;
        }
        if (StringUtils.isEmpty(value)) {
            return true;
        }
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith(XLSX) || fileName.endsWith(xlsx)) {
            Workbook book = new XSSFWorkbook(file.getInputStream());
            return hasValueInRow(book, sheetName, rowIndex, value);
        }
        if (fileName.endsWith(XLS) || fileName.endsWith(xls)) {
            Workbook book = new HSSFWorkbook(file.getInputStream());
            return hasValueInRow(book, sheetName, rowIndex, value);
        }
        return false;
    }

    /**
     * 判断当前行是否包含对应的值
     *
     * @param book
     * @param sheetName
     * @param rowIndex
     * @param value
     * @return
     * @throws Exception
     */
    public static boolean hasValueInRow(Workbook book, String sheetName, Integer rowIndex, String value) {
        Sheet sheet = null;
        if (StringUtils.isNotEmpty(sheetName)) {
            sheet = book.getSheet(sheetName);
        }
        if (null == sheet) {
            sheet = book.getSheetAt(0);
        }
        if (null == rowIndex) {
            rowIndex = 0;
        }
        Map<Integer, String> titleMap = getSingleTitle(sheet.getRow(rowIndex)); // 首行为标题行
        if (0 == titleMap.size()) {
            return false;
        }
        return titleMap.values().contains(value);
    }


    /**
     * 读取excel
     *
     * @param file         文件上传
     * @param readRowStart
     * @param titleMap
     * @return
     * @throws Exception
     */
    public static Map<String, Object> readExcel(MultipartFile file, Integer readRowStart, Map<String, String> titleMap) throws Exception {
        if (null == file) {
            return null;
        }
        Map<String, Object> map = new HashMap<>();
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith(XLSX) || fileName.endsWith(xlsx)) {
            Workbook book = new XSSFWorkbook(file.getInputStream());
            Map<Integer, String> keyMap = getSingleTitle(book.getSheetAt(0).getRow(0));
            map.put("data", readSheet(book.getSheetAt(0), readRowStart, titleMap));
            map.put("keyMap", keyMap);
            return map;
        }
        if (fileName.endsWith(XLS) || fileName.endsWith(xls)) {
            Workbook book = new HSSFWorkbook(file.getInputStream());
            Map<Integer, String> keyMap = getSingleTitle(book.getSheetAt(0).getRow(0));
            map.put("data", readSheet(book.getSheetAt(0), readRowStart, titleMap));
            map.put("keyMap", keyMap);
            return map;
        }
        return null;
    }

    /**
     * 读取excel
     *
     * @param file         文件上传
     * @param readRowStart
     * @return
     * @throws Exception
     */
    public static Map<String, Object> readCCBBankExcel(MultipartFile file, Integer readRowStart) throws Exception {
        if (null == file) {
            return null;
        }
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith(XLS) || fileName.endsWith(xls)) {
            Workbook book = new HSSFWorkbook(file.getInputStream());
            return readCCBBankSheet(book.getSheetAt(0), readRowStart);
        }
        return null;
    }

    /**
     * 读取sheet
     *
     * @param sheet
     * @param readRowStart
     * @return
     * @throws IOException
     */
    public static Map<String, Object> readCCBBankSheet(Sheet sheet, Integer readRowStart) throws IOException {

        int rowStart = sheet.getFirstRowNum();       // 首行下标
        int rowEnd = sheet.getLastRowNum();          // 尾行下标
        if (rowStart == rowEnd) {                    // 如果首行与尾行相同，表明只有一行，直接返回空数组
            return new HashMap<>();
        }
        if (rowEnd == 9) {
            return new HashMap<>();
        }
        Map<String, Object> data = new HashMap<>();

        //银行名称
        Row row1 = sheet.getRow(0);
        Object val1 = getCellValue(row1.getCell(0));
        data.put("bankName", val1.toString());
        Row row2 = sheet.getRow(4);
        //银行账号
        Object val2 = getCellValue(row2.getCell(1));
        data.put("bankAccount", val2.toString());
        Map<Integer, String> keyMap = getSingleTitle(sheet.getRow(7)); // 首行为标题行
        data.put("keyMap", keyMap);
        Map<String, String> titleMap = new HashMap<>();
        titleMap.put("记账日期", "payDate");
        titleMap.put("对方户名", "payeeName");
        titleMap.put("对方账号", "payeeAccount");
        titleMap.put("摘要", "text");
        titleMap.put("备注", "remark");
        //借方/支出
        titleMap.put("发生额/元", "outQty");
        //贷方/收入
        titleMap.put("null", "inQty");
        titleMap.put("余额/元", "balance");
        data.put("titleMap", titleMap);
        // 获取每行JSON对象的
        // 值
        JSONArray array = new JSONArray();
        for (int i = readRowStart; i <= rowEnd; i++) {
            Row row = sheet.getRow(i);
            if (null == row) {
                continue;
            }
            JSONObject jsonObject = new JSONObject();
            int colSize = row.getLastCellNum();
            for (int j = 0; j < colSize; j++) {
                Cell cell = row.getCell(j);
                if (null == cell) {
                    continue;
                }
                Object val = getCellValue(cell);
                String title = titleMap.get(keyMap.get(j));
                if (null == title) {
                    continue;
                }
                if (null == val || val.equals("--")) {
                    continue;
                }
                jsonObject.set(title, val);
            }
            if (jsonObject.size() != 0) {
                array.add(jsonObject);
            }
        }
        data.put("data", array);
        return data;
    }

    /**
     * 读取sheet
     *
     * @param sheet
     * @param readRowStart
     * @param titleMap
     * @return
     * @throws IOException
     */
    public static JSONArray readSheet(Sheet sheet, Integer readRowStart, Map<String, String> titleMap) throws IOException {

        int rowStart = sheet.getFirstRowNum();       // 首行下标
        int rowEnd = sheet.getLastRowNum();          // 尾行下标
        if (rowStart == rowEnd) {                    // 如果首行与尾行相同，表明只有一行，直接返回空数组
            return new JSONArray();
        }

        Map<Integer, String> keyMap = getSingleTitle(sheet.getRow(rowStart)); // 首行为标题行

        // 获取每行JSON对象的值
        JSONArray array = new JSONArray();
        if (null == readRowStart) {
            readRowStart = 1;
        }
        for (int i = readRowStart; i <= rowEnd; i++) {
            Row eachRow = sheet.getRow(i);
            if (null == eachRow) {
                continue;
            }
            JSONObject obj = getRowValue(eachRow, keyMap, titleMap);
            if (null != obj) {
                array.add(obj);
            }
        }
        return array;
    }

    /**
     * 读取excel
     *
     * @param file 文件上传
     * @return
     * @throws Exception
     */
    public static Map<String, Object> readABCBankExcel(MultipartFile file) throws Exception {
        if (null == file) {
            return null;
        }
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith(XLS) || fileName.endsWith(xls)) {
            Workbook book = new HSSFWorkbook(file.getInputStream());
            return readABCBankSheet(book.getSheetAt(0));
        }
        return null;
    }


    /**
     * 读取excel
     *
     * @param file 文件上传
     * @return
     * @throws Exception
     */
    public static Map<String, Object> readICBCBankExcel(MultipartFile file) throws Exception {
        if (null == file) {
            return null;
        }

        InputStreamReader reader = new InputStreamReader(file.getInputStream(), Charset.forName("GBK"));
        CSVReader csvReader = new CSVReaderBuilder(reader).build();
        List<String[]> list = csvReader.readAll();

        for (String[] strings : list) {
            System.out.println(strings.toString());
        }


        String fileName = file.getOriginalFilename();
        if (fileName.endsWith(CSV) || fileName.endsWith(csv)) {
            return readICBCBankSheet(list);
        }
        return null;
    }

    /**
     * 读取excel
     *
     * @param file 文件上传
     * @return
     * @throws Exception
     */
    public static Map<String, Object> readBocBankExcel(MultipartFile file) throws Exception {
        if (null == file) {
            return null;
        }
        String fileName = file.getOriginalFilename();

        if (fileName.endsWith(XLS) || fileName.endsWith(xls)) {
            Workbook book = new HSSFWorkbook(file.getInputStream());
            return readBocBankSheet(book.getSheetAt(0));
        }
        return null;
    }

    /**
     * 读取excel
     *
     * @param file 文件上传
     * @return
     * @throws Exception
     */
    public static Map<String, Object> readCMBCBankExcel(MultipartFile file) throws Exception {
        if (null == file) {
            return null;
        }
        String fileName = file.getOriginalFilename();

        if (fileName.endsWith(XLSX) || fileName.endsWith(xlsx)) {
            Workbook book = new XSSFWorkbook(file.getInputStream());
            return readCMBCBankSheet(book.getSheetAt(0));
        }
        return null;
    }


    /**
     * 读取excel
     *
     * @param file 文件上传
     * @return
     * @throws Exception
     */
    public static Map<String, Object> readPSBCankExcel(MultipartFile file) throws Exception {
        if (null == file) {
            return null;
        }
        String fileName = file.getOriginalFilename();

        if (fileName.endsWith(XLS) || fileName.endsWith(xls)) {
            Workbook book = new HSSFWorkbook(file.getInputStream());
            return readPSBCBankSheet(book.getSheetAt(0));
        }
        return null;
    }


    /**
     * 读取excel
     *
     * @param file 文件上传
     * @return
     * @throws Exception
     */
    public static Map<String, Object> readBCMankExcel(MultipartFile file) throws Exception {
        if (null == file) {
            return null;
        }
        String fileName = file.getOriginalFilename();

        if (fileName.endsWith(XLS) || fileName.endsWith(xls)) {
            Workbook book = new HSSFWorkbook(file.getInputStream());
            return readBCMBankSheet(book.getSheetAt(0));
        }
        return null;
    }


    /**
     * 读取excel
     *
     * @param file 文件上传
     * @return
     * @throws Exception
     */
    public static Map<String, Object> readOTHERBankExcel(MultipartFile file) throws Exception {
        if (null == file) {
            return null;
        }
        String fileName = file.getOriginalFilename();

        if (fileName.endsWith(XLSX) || fileName.endsWith(xlsx)) {
            Workbook book = new XSSFWorkbook(file.getInputStream());
            return readOTHERBankSheet(book.getSheetAt(0));
        }
        return null;
    }

    /**
     * 读取excel
     *
     * @param file 文件上传
     * @return
     * @throws Exception
     */
    public static Map<String, Object> readkWechatWalletExcel(MultipartFile file) throws Exception {
        if (null == file) {
            return null;
        }

        InputStreamReader reader = new InputStreamReader(file.getInputStream(), Charset.forName("GBK"));
        CSVReader csvReader = new CSVReaderBuilder(reader).build();
        List<String[]> list = csvReader.readAll();

        for (String[] strings : list) {
            System.out.println(strings.toString());
        }

        String fileName = file.getOriginalFilename();
        if (fileName.endsWith(CSV) || fileName.endsWith(csv)) {
            return readWechaWalletSheet(list);
        }
        return null;
    }


    /**
     * 读取ABC农行sheet
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, Object> readABCBankSheet(Sheet sheet) throws IOException {
        Integer readRowStart = 3;
        Integer endRowGap = 2;
        int rowStart = sheet.getFirstRowNum();       // 首行下标
        int rowEnd = sheet.getLastRowNum();          // 尾行下标
        if (rowStart == rowEnd) {                    // 如果首行与尾行相同，表明只有一行，直接返回空数组
            return new HashMap<>();
        }
        if (rowEnd == 2) {
            return new HashMap<>();
        }

        //银行账号
        Row row1 = sheet.getRow(1);

        Object val1 = getCellValue(row1.getCell(0));
        Map<String, Object> data = new HashMap<>();
        data.put("bankAccount", val1.toString().split(":")[1].replaceAll("-", ""));
        Map<String, String> titleMap = new HashMap<>();
        titleMap.put("交易时间", "payDate");
        titleMap.put("对方户名", "payeeName");
        titleMap.put("对方账号", "payeeAccount");
        titleMap.put("交易用途", "text");
        //借方/支出
        titleMap.put("支出金额", "outQty");
        //贷方/收入
        titleMap.put("收入金额", "inQty");
        titleMap.put("账户余额", "balance");
        data.put("titleMap", titleMap);
        Map<Integer, String> keyMap = getSingleTitle(sheet.getRow(readRowStart - 1)); // 首行为标题行
        data.put("keyMap", keyMap);
        return readLocalBankSheet(sheet, 3, 2, data, rowEnd, 0);
    }


    /**
     * 读取ICBC工行sheet
     *
     * @param list
     * @return
     * @throws IOException
     */
    public static Map<String, Object> readICBCBankSheet(List<String[]> list) throws IOException {
        Integer readRowStart = 5;
        // 尾行下标
        if (list.size() <= 1) {                    // 如果首行与尾行相同，表明只有一行，直接返回空数组
            return new HashMap<>();
        }

        Map<String, Object> data = new HashMap<>();
        String account = list.get(0)[1];
        data.put("bankAccount", list.get(0)[1].replaceAll("\t", "").replaceAll("\"", ""));

        Map<String, String> titleMap = new HashMap<>();
        titleMap.put("日期", "payDate");
        titleMap.put("对方户名", "payeeName");
        titleMap.put("对方账号", "payeeAccount");
        titleMap.put("摘要", "text");
        //借方/支出
        titleMap.put("借方发生额", "outQty");
        //贷方/收入
        titleMap.put("贷方发生额", "inQty");
        titleMap.put("余额", "balance");
        data.put("titleMap", titleMap);
        Map<Integer, String> keyMap = new HashMap<>();
        Integer integer = 0;
        for (String item : list.get(4)) {
            keyMap.put(integer, item);
            integer++;
        }
        data.put("keyMap", keyMap);

        // 获取每行JSON对象的
        // 值
        JSONArray array = new JSONArray();
        for (int i = readRowStart; i <= list.size() - 1; i++) {
            String[] row = list.get(i);
            if (null == row) {
                continue;
            }
            JSONObject jsonObject = new JSONObject();
            int colSize = 10;
            for (int j = 0; j < colSize; j++) {
                String cell = row[j];
                if (null == cell) {
                    continue;
                }

                String title = titleMap.get(keyMap.get(j));
                if (null == title) {
                    continue;
                }
                jsonObject.set(title, cell.replaceAll("\t", ""));
            }
            if (jsonObject.size() != 0) {
                array.add(jsonObject);
            }
        }
        data.put("data", array);
        return data;
    }

    /**
     * 读取ICBC工行sheet
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, Object> readBocBankSheet(Sheet sheet) throws IOException {
        Integer readRowStart = 9;
        Integer endRowGap = 0;
        int rowStart = sheet.getFirstRowNum();       // 首行下标
        int rowEnd = sheet.getLastRowNum();          // 尾行下标
        if (rowStart == rowEnd) {                    // 如果首行与尾行相同，表明只有一行，直接返回空数组
            return new HashMap<>();
        }
        if (rowEnd == readRowStart - 1) {
            return new HashMap<>();
        }

        //银行账号
        Row row1 = sheet.getRow(1);
        Object val1 = getCellValue(row1.getCell(1));
        Map<String, Object> data = new HashMap<>();
        data.put("bankAccount", val1);
        Map<String, String> titleMap = new HashMap<>();
        titleMap.put("交易日期[ Transaction Date ]", "payDate");
        titleMap.put("收款人名称[ Payee's Name ]", "payeeName");
        titleMap.put("收款人账号[ Payee's Account Number ]", "payeeAccount");
        titleMap.put("业务类型[ Business type ]", "businessType");
        titleMap.put("摘要[ Reference ]", "reference");
        titleMap.put("用途[ Purpose ]", "purpose");
        titleMap.put("交易附言[ Remark ]", "remark");
        titleMap.put("交易金额[ Trade Amount ]", "amount");
        titleMap.put("交易后余额[ After-transaction balance ]", "balance");
        data.put("titleMap", titleMap);
        Map<Integer, String> keyMap = getSingleTitle(sheet.getRow(readRowStart - 1)); // 首行为标题行
        data.put("keyMap", keyMap);
        readLocalBankSheet(sheet, readRowStart, endRowGap, data, rowEnd, 0);
        return data;
    }

    /**
     * 读取CBC工行sheet
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, Object> readCMBCBankSheet(Sheet sheet) throws IOException {
        Integer readRowStart = 13;
        Integer endRowGap = 0;
        int rowStart = sheet.getFirstRowNum();       // 首行下标
        int rowEnd = sheet.getLastRowNum();          // 尾行下标
        if (rowStart == rowEnd) {                    // 如果首行与尾行相同，表明只有一行，直接返回空数组
            return new HashMap<>();
        }
        if (rowEnd == readRowStart - 1) {
            return new HashMap<>();
        }

        //银行账号
        Row row1 = sheet.getRow(5);
        Object val1 = getCellValue(row1.getCell(7));
        Map<String, Object> data = new HashMap<>();
        data.put("bankAccount", val1);
        Map<String, String> titleMap = new HashMap<>();
        titleMap.put("交易日", "payDate");
        titleMap.put("收(付)方名称", "payeeName");
        titleMap.put("收(付)方账号", "payeeAccount");
        titleMap.put("摘要", "text");
        //借方/支出
        titleMap.put("借方金额", "outQty");
        //贷方/收入
        titleMap.put("贷方金额", "inQty");
        titleMap.put("余额", "balance");
        data.put("titleMap", titleMap);
        Map<Integer, String> keyMap = getSingleTitle(sheet.getRow(readRowStart - 1)); // 首行为标题行
        data.put("keyMap", keyMap);
        return readLocalBankSheet(sheet, readRowStart, endRowGap, data, rowEnd, 0);
    }

    /**
     * 读取PSBC邮储sheet
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, Object> readPSBCBankSheet(Sheet sheet) throws IOException {
        Integer readRowStart = 5;
        Integer endRowGap = 0;
        int rowStart = sheet.getFirstRowNum();       // 首行下标
        int rowEnd = sheet.getLastRowNum();          // 尾行下标
        if (rowStart == rowEnd) {                    // 如果首行与尾行相同，表明只有一行，直接返回空数组
            return new HashMap<>();
        }
        if (rowEnd == readRowStart - 1) {
            return new HashMap<>();
        }

        //银行账号
        Row row1 = sheet.getRow(1);
        Object val1 = getCellValue(row1.getCell(1));
        Map<String, Object> data = new HashMap<>();
        data.put("bankAccount", val1);
        Map<String, String> titleMap = new HashMap<>();
        titleMap.put("交易日期", "payDate");
        titleMap.put("对方户名", "payeeName");
        titleMap.put("对方账号", "payeeAccount");
        titleMap.put("摘要", "text");
        //借方/支出
        titleMap.put("支出金额", "outQty");
        //贷方/收入
        titleMap.put("收入金额", "inQty");
        titleMap.put("余额", "balance");
        titleMap.put("附言", "remark");
        data.put("titleMap", titleMap);
        Map<Integer, String> keyMap = getSingleTitle(sheet.getRow(readRowStart - 1)); // 首行为标题行
        data.put("keyMap", keyMap);
        return readLocalBankSheet(sheet, readRowStart, endRowGap, data, rowEnd, 0);
    }

    /**
     * 读取PSBC邮储sheet
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, Object> readBCMBankSheet(Sheet sheet) throws IOException {
        Integer readRowStart = 2;
        Integer endRowGap = 1;
        int rowStart = sheet.getFirstRowNum();       // 首行下标
        int rowEnd = sheet.getLastRowNum();          // 尾行下标
        if (rowStart == rowEnd) {                    // 如果首行与尾行相同，表明只有一行，直接返回空数组
            return new HashMap<>();
        }
        if (rowEnd == readRowStart - 1) {
            return new HashMap<>();
        }

        //银行账号
        Row row1 = sheet.getRow(0);
        Object val1 = getCellValue(row1.getCell(1));
        Map<String, Object> data = new HashMap<>();
        data.put("bankAccount", val1);
        Map<String, String> titleMap = new HashMap<>();
        titleMap.put("交易时间", "payDate");
        titleMap.put("对方户名", "payeeName");
        titleMap.put("对方账号", "payeeAccount");
        titleMap.put("摘要", "text");
        //借方/支出
        titleMap.put("借方发生额（支出）", "outQty");
        //贷方/收入
        titleMap.put("贷方发生额（收入）", "inQty");
        titleMap.put("账户余额", "balance");
        data.put("titleMap", titleMap);
        Map<Integer, String> keyMap = getSingleTitle(sheet.getRow(readRowStart - 1)); // 首行为标题行
        data.put("keyMap", keyMap);
        return readLocalBankSheet(sheet, readRowStart, endRowGap, data, rowEnd, 0);
    }

    /**
     * 读取PSBC邮储sheet
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, Object> readOTHERBankSheet(Sheet sheet) throws IOException {
        Integer readRowStart = 4;
        Integer endRowGap = 1;
        // 首行下标
        int rowStart = sheet.getFirstRowNum();
        // 尾行下标
        int rowEnd = sheet.getLastRowNum();
        // 如果首行与尾行相同，表明只有一行，直接返回空数组
        if (rowStart == rowEnd) {
            return new HashMap<>();
        }
        if (rowEnd == readRowStart - 1) {
            return new HashMap<>();
        }

        //银行账号
        Row row1 = sheet.getRow(1);
        Object val1 = getCellValue(row1.getCell(1));
        Map<String, Object> data = new HashMap<>();
        data.put("bankAccount", val1);
        Map<String, String> titleMap = new HashMap<>();
        titleMap.put("日期", "payDate");
        titleMap.put("对方名称", "payeeName");
        titleMap.put("内容", "text");
        titleMap.put("款项类型", "payType");
        //借方/支出
        titleMap.put("借方金额", "outQty");
        //贷方/收入
        titleMap.put("贷方金额", "inQty");
        titleMap.put("余额", "balance");
        data.put("titleMap", titleMap);
        // 首行为标题行
        Map<Integer, String> keyMap = getSingleTitle(sheet.getRow(readRowStart - 1));
        data.put("keyMap", keyMap);
        return readLocalBankSheet(sheet, readRowStart, endRowGap, data, rowEnd, 2);
    }

    /**
     * 读取WechaWalletsheet
     *
     * @param list
     * @return
     * @throws IOException
     */
    public static Map<String, Object> readWechaWalletSheet(List<String[]> list) throws IOException {
        Integer readRowStart = 1;

        if (list.size() <= 1) {
            return new HashMap<>();
        }


        Map<String, Object> data = new HashMap<>();
        Map<String, String> titleMap = new HashMap<>();
        titleMap.put("日期", "payDate");
        titleMap.put("业务类型", "text");
        //借方/支出
        titleMap.put("收入金额(元)", "inQty");
        //贷方/收入
        titleMap.put("支出金额(元)", "outQty");
        titleMap.put("收支净额(元)", "balance");
        data.put("titleMap", titleMap);
        // 首行为标题行
        Map<Integer, String> keyMap = new HashMap<>();
        Integer integer = 0;
        for (String item : list.get(0)) {
            keyMap.put(integer, item);
            integer++;
        }
        data.put("keyMap", keyMap);

//        return readLocalBankSheet(sheet, readRowStart, endRowGap, data, rowEnd, 0);
        // 获取每行JSON对象的
        // 值
        JSONArray array = new JSONArray();
        for (int i = readRowStart; i < list.size(); i++) {
            boolean isContinue = true;
            String[] row = list.get(i);
            if (null == row) {
                continue;
            }
            JSONObject jsonObject = new JSONObject();
            int colSize = 7;
            for (int j = 0; j < colSize; j++) {
                String cell = row[j];
                if (null == cell || "".equals(cell)) {
                    continue;
                }

                String title = titleMap.get(keyMap.get(j));
                if (null == title) {
                    continue;
                }
                if ("outQty".equals(title) || "inQty".equals(title) || "balance".equals(title)) {
                    if (!CommonUtil.isNumeric(cell.replaceAll("\t", ""))) {
                        isContinue = false;
                        continue;
                    }
                }
                jsonObject.set(title, cell.replaceAll("\t", ""));
            }
            if (jsonObject.size() != 0 && isContinue) {
                if (i == list.size() - 1) {
                    String tt = (String) jsonObject.get("payDate");
                    if (tt != null && tt.contains("合计")) {
                        continue;
                    }
                }
                array.add(jsonObject);
            }
        }
        data.put("data", array);
        return data;
    }

    /**
     * 读取行标题
     *
     * @param row
     * @return
     */
    public static Map<Integer, String> getSingleTitle(Row row) {

        int cellStart = row.getFirstCellNum();
        int cellEnd = row.getLastCellNum();

        Map<Integer, String> keyMap = new HashMap<>();
        for (int j = cellStart; j < cellEnd; j++) {
            if (StringUtils.isEmpty((String) getCellValue(row.getCell(j)))) {
                keyMap.put(j, "null");
            } else {
                keyMap.put(j, (String) getCellValue(row.getCell(j)));
            }
        }
        return keyMap;
    }


    /**
     * 读取行数据
     *
     * @param row      数据行
     * @param keyMap   列标题（j,标题）   不可为空
     * @param titleMap 对应属性名（标题,属性名）
     * @return
     */
    public static JSONObject getRowValue(Row row, Map<Integer, String> keyMap, Map<String, String> titleMap) {

        JSONObject jsonObject = new JSONObject();
        int colSize = row.getLastCellNum();
        StringBuffer sb = new StringBuffer();
        if (null == titleMap) {
            titleMap = new HashMap<>();
        }
        for (int j = 0; j < colSize; j++) {
            Cell cell = row.getCell(j);
            if (null == cell) {
                continue;
            }
            Object val = getCellValue(cell);
            if (null != val) {
                sb.append(val.toString());                   // 所有数据添加到里面，用于判断该行是否为空
            }
            String title = titleMap.get(keyMap.get(j));
            if (null == title || "".equals(title)) {
                title = keyMap.get(j);
            }
            if (null == title) {
                continue;
            }
            jsonObject.put(title, val);
        }
        if ("".equals(sb.toString().trim())) {
            return null;
        }
        return jsonObject;
    }


    /**
     * 读取单元格的数据
     *
     * @param cell
     * @return
     */
    private static Object getCellValue(Cell cell) {
        if (null == cell) {
            return null;
        }
        switch (cell.getCellType().getCode()) {
            case 0: // 数字
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    return date;
                } else {
                    Object inputValue;
                    Long longVal = Math.round(cell.getNumericCellValue());
                    Double doubleVal = cell.getNumericCellValue();
                    if (Double.parseDouble(longVal + ".0") == doubleVal) {   //判断是否含有小数位.0
                        inputValue = longVal;
                    } else {
                        inputValue = doubleVal;
                    }
                    return inputValue;
                }
            case 1:// 字符串
                String value = cell.getStringCellValue();
                if (null == value || "".equals(value.trim())) {
                    return null;
                }
                return value.trim();
            case 4:        // Boolean
                return cell.getBooleanCellValue();
            case 2: // 公式
                try {
                    Long longVal = Math.round(cell.getNumericCellValue());
                    Double doubleVal = cell.getNumericCellValue();
                    if (Double.parseDouble(longVal + ".0") == doubleVal) {   //判断是否含有小数位.0
                        return longVal;
                    } else {
                        return doubleVal;
                    }
                } catch (IllegalStateException e) {
                    cell.setCellType(CellType.STRING);
                    return cell.getStringCellValue();
                }
            default:
                break;
        }
        return null;
    }


    /**
     * 读取本地银行sheet通用方法
     *
     * @param sheet
     * @param readRowStart
     * @return
     * @throws IOException
     */
    public static Map<String, Object> readLocalBankSheet(Sheet sheet, Integer readRowStart, Integer endRowGap, Map<String, Object> data, Integer rowEnd, Integer colSizeGap) throws IOException {

        Map<Integer, String> keyMap = (Map<Integer, String>) data.get("keyMap");
        ; // 首行为标题行
        Map<String, String> titleMap = (Map<String, String>) data.get("titleMap");
        // 获取每行JSON对象的
        // 值
        JSONArray array = new JSONArray();
        for (int i = readRowStart; i <= rowEnd - endRowGap; i++) {
            boolean isContinue = true;
            Row row = sheet.getRow(i);
            if (null == row) {
                continue;
            }
            JSONObject jsonObject = new JSONObject();
            int colSize = row.getLastCellNum();
            for (int j = 0; j < colSize - colSizeGap; j++) {
                Cell cell = row.getCell(j);
                if (null == cell) {
                    continue;
                }
                Object val = getCellValue(cell);
                String title = titleMap.get(keyMap.get(j));
                if (null == title) {
                    continue;
                }
                if ("outQty".equals(title) || "inQty".equals(title) || "balance".equals(title)) {
                    if (null != val && !CommonUtil.isNumeric(val.toString())) {
                        isContinue = false;
                        continue;
                    }
                }
                jsonObject.set(title, val);
            }
            if (jsonObject.size() != 0 && isContinue) {
                array.add(jsonObject);
            }
        }
        data.put("data", array);
        return data;
    }

}
